package com.agu.Excel;

import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.text.DateFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import com.alibaba.druid.util.StringUtils;

public class ExcelReader {

	public enum ExcelType {
		XLS, XLSX
	}

	/**
	 * 判断指定的单元格是否是合并单元格
	 * 
	 * @param sheet
	 * @param row
	 *            行下标
	 * @param column
	 *            列下标
	 * @return
	 */
	private static boolean isMergedRegion(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();
		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress range = sheet.getMergedRegion(i);

			int firstColumn = range.getFirstColumn();
			int lastColumn = range.getLastColumn();
			int firstRow = range.getFirstRow();
			int lastRow = range.getLastRow();
			if (row >= firstRow && row <= lastRow) {
				if (column >= firstColumn && column <= lastColumn) {
					return true;
				}
			}
		}
		return false;
	}

	public static String tablesql(Sheet sheet,String table,int year,int firstcell,int lastcell) throws Exception {
		 
 		long now11= System.currentTimeMillis();

//		InputStream inputStream = new FileInputStream(
//				// "/Users/neilhaha/qqdownload/校友分会信息.xlsx");
//				"/Users/neilhaha/Desktop/tables/2018.3.8最新校友信息总表.xls");
//
//		Workbook wb = createWb(inputStream, ExcelType.XLS);
//
//	   
//		Sheet sheet = HaHaExcelReader.getSheet(wb, 0);
	  
//		List<String[]> list = getCells(sheet, true);
 		
 		
		// 遍历Sheet
		List<String[]> list = ExcelReader.getCells(sheet, 0,1, firstcell-1, lastcell-1, false);
		int i = 0;
		int jj=0;
 		String sql="create table "+table+"( id number(10) not null,year number(8) not null,%ss%,create_time date default sysdate not null );\r\n alter table "+table+" add primary key(id);";
		String insertsql="insert into "+table+"(id,year,create_time,%columns%)values(seq_lanzhou.nextval,"+year+",sysdate,%columns__%)";
		StringBuffer sb=new StringBuffer();
		StringBuffer columns=new StringBuffer();
		StringBuffer columns__=new StringBuffer();
		for (Object[] cells : list) {
			i++;
 
			if(i>1)
				break;
			int len=cells.length;
			int j=0;
			for (Object cell : cells) {
		 
				j++;
				String s="";
				if(cell!=null)
					s=cell.toString().replace(" ", "").replace("　", "").trim();
  
				//String name=PingYinUtil.getEachFirstSpell(s)+"_"+j;
				//sb.append(name+" varchar2(100)");
			
				//columns.append(name);
				columns__.append("?");
				if(j<len) {
					sb.append(",");
					columns.append(",");
					columns__.append(",");
				}
				
			}

		}
		sql=sql.replace("%ss%", sb.toString());
		insertsql=insertsql.replace("%columns%", columns.toString());
		insertsql=insertsql.replace("%columns__%", columns__.toString());

		System.out.println(sql);
		
		System.out.println(insertsql);
		
		return insertsql;
		
		

	}
	public static void main(String[] args) throws Exception {
		Class.forName("oracle.jdbc.driver.OracleDriver");
	
		long now11= System.currentTimeMillis();

		InputStream inputStream = new FileInputStream(
				// "/Users/neilhaha/qqdownload/校友分会信息.xlsx");
				"/Users/neilhaha/Desktop/tables/提供给信息中心-2018年秋季家庭经济困难学生信息统计表.xlsx");

		Workbook wb = createWb(inputStream, ExcelType.XLSX);

		// 获取Workbook中Sheet个数
//		int sheetTotal = wb.getNumberOfSheets();

		// System.out.println("工作簿中的工作表个数为：{" + sheetTotal + "}");
		// 获取Sheet
		Sheet sheet = ExcelReader.getSheet(wb, 0);
//		String sql = "insert into xls_xiaoyou(ID,LOGIN_NAME,xingming,GENDER,BIRTHDAY,COUNTRY,MINZU,ZHIWU,DANWEI,HANGYE,DIZHI,SHENG,SHI,DIANHUA,"
//				+ "WEIXIN,QQ,MAIL,MOBILE,XUELI,GUANLIXUEYUAN,XUEJIXUEYUAN,ZHUANYE,RUXUE,BIYE,XUEHAO,JIATINGDIANHUA,QQQUN,YEAR,"
//				+ "CREATE_TIME) values(seq_lanzhou.nextval,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,'2018',sysdate)";
//		String sql=HaHaExcelReader.tablesql("xls_biyesheng",2018);
		int cellsnum=13;
		String sql=ExcelReader.tablesql(sheet, "xls_benkesheng",2018, 1, cellsnum);
		
		System.out.println(sql);
		
//		System.exit(-1);
		
		Connection con = DriverManager.getConnection("jdbc:oracle:thin:@47.106.82.217:1521:gree", "lzlg", "lzlg123");
		System.out.println(con);
		PreparedStatement ps = con.prepareStatement(sql);
int cellno=12;//从0开始算
int lastrowno=22078; //从0开始算
		List<String[]> list = ExcelReader.getCells(sheet, 0, lastrowno, 0, cellno, true);
		// 遍历Sheet
//		List<String[]> list = HaHaExcelReader.getOriginCells(sheet, true);
		int i = 0;
		int jj=0;
		int jjj=0;
		for (Object[] cells : list) {
			i++;

			if(i==1) {
				continue;
			}

			int len = cells.length;
			if (len <cellsnum) {
				jj++;
//				System.out.println(len);
				continue;
			}
			int ii = 0;
			for (Object cell : cells) {
				ii++;
			 
				String s="";
				if(cell!=null)
					s=cell.toString().replace(" ", "").replace("　", "").trim();
//				System.out.print(ii+":"+s+",");
				ps.setString(ii, s);

			}
//			System.out.println("\r\n");
			ps.addBatch();
			long now = System.currentTimeMillis();
			if ((i % 100) == 0) {
				ps.executeBatch();
				ps.clearBatch();
				jjj++;
				System.out.println("插入100条" + (System.currentTimeMillis() - now));
			}
			// System.out.print(cells.length+"\t"+i+"\n");
		}
		System.out.println(jj);
		ps.executeBatch();
		ps.clearBatch();
		System.out.println("最后：：：" + (System.currentTimeMillis() - now11)+"===="+jjj);

		inputStream.close();
	}
	public static void main2(String[] args) throws Exception {
		String file =
				// "/Users/neilhaha/others/xiaoyunliang/兰州/项目输出文件0720/考核表/【人事处】岗位津贴表.xlsx";
				// "/Users/neilhaha/others/xiaoyunliang/兰州/项目输出文件0720/考核表/【科技处】科研直接奖励汇总表.xlsx";

				"/Users/neilhaha/others/xiaoyunliang/兰州/数据调研0528/01学校办公室/本科教学监测/表1-3学校相关党政单位.xls";
		// "/Users/neilhaha/others/xiaoyunliang/兰州/项目输出文件0720/考核表/【人事处】上岗及岗位完成情况.xls");

		// "/Users/neilhaha/Desktop/a.xlsx");

		Workbook wb = createWb(file);

		// 获取Workbook中Sheet个数
		int sheetTotal = wb.getNumberOfSheets();

		System.out.println("工作簿中的工作表个数为：{" + sheetTotal + "}");

		// 获取Sheet
		// Sheet sheet = ExcelReader.getSheet(wb, 10);
		Sheet sheet = ExcelReader.getSheet(wb, 0);

		// 遍历Sheet
		List<String[]> list = getCells(sheet, 2 - 1 - 1, 2 - 1 - 1, 0, 6, false);
		// Map map = new HashMap();
		int j = 0;
		for (Object[] cells : list) {
			int i = 0;
			System.out.print((j++) + "行:");
			for (Object cell : cells) {
				if (cell == null)
					continue;
				String s = cell.toString();
				// if(s==null||s.equals("")){//要拿上一行的值
				// if(map.containsKey(i))
				// s=map.get(i).toString();
				// }else {
				// map.put(i, s.toString());
				// }

				System.out.print(s + "(" + (i++) + "),");
			}
			System.out.println("");
		}
	}

	/**
	 * 创建工作簿对象
	 * 
	 * @param filePath
	 * @return
	 * @throws IOException
	 * @date 2013-5-11
	 */
	public static Workbook createWb(InputStream inputStream, ExcelType type) {
		try {
			if (type == null || inputStream == null) {
				return null;
			} else if (type.equals(ExcelType.XLS)) {
				return new HSSFWorkbook(inputStream);
			} else if (type.equals(ExcelType.XLSX)) {
				return new XSSFWorkbook(inputStream);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Workbook createWb(String fileName) {
		String s = fileName.toLowerCase();

		try {
			if (s.endsWith("xls")) {
				return new HSSFWorkbook(new FileInputStream(fileName));
			} else if (s.endsWith("xlsx")) {
				return new XSSFWorkbook(new FileInputStream(fileName));
			} else {
				return null;
			}
		} catch (Exception e) {
			e.printStackTrace();
		}
		return null;
	}

	public static Sheet getSheet(Workbook wb, String sheetName) {
		return wb.getSheet(sheetName);
	}

	public static Sheet getSheet(Workbook wb, int index) {
		return wb.getSheetAt(index);
	}

	public static List<String[]> getCells(Sheet sheet, int firstRow, int lastRow, int firstCell, int lastCell,
			boolean isShiftEmptyRow) {
		// List ds = sheet.getDataValidations();
		// if (ds != null) {
		// for (int i = 0; i < ds.size(); i++) {
		// DataValidation dv = (DataValidation) ds.get(i);
		// String[] ss = dv.getValidationConstraint().getExplicitListValues();
		// for (String sss : ss) {
		// System.out.println(sss + ",,,");
		// }
		// }
		// }
//System.out.println("lastCell==="+lastCell);
		// 去掉空行。这个去掉空行不靠谱
		if (isShiftEmptyRow) {
			int i = sheet.getLastRowNum();
			Row tempRow;
			while (i > 0) {
				i--;
				tempRow = sheet.getRow(i);
				if (tempRow == null) {
					sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
				} else {
					boolean isEmpty = true;
					for (int c = tempRow.getFirstCellNum(); c <= tempRow.getLastCellNum(); c++) {
						Cell cell = tempRow.getCell(c);
						if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
							isEmpty = false;
							continue;
						}
					}
					if (isEmpty) {
						sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
					}
				}
			}
		}
		// 去掉空行。
		List<String[]> list = new ArrayList<String[]>();
		if (firstRow == 0)
			firstRow = sheet.getFirstRowNum();
		if (lastRow <= 0 || lastRow < firstRow)
			lastRow = sheet.getLastRowNum();
//System.out.println("lastrow;;;;;;;;;"+lastRow+",,,"+firstRow);
		for (int r = firstRow; r <= lastRow; r++) {
			Row row = sheet.getRow(r);
			if (row == null) {
				System.out.println("row is null:" + r);
				continue;
			}
//			 System.out.println(firstCell+"==------"+row.getFirstCellNum()+",,,"+row.getRowNum()+",,,"+lastCell+",,"+row.getLastCellNum());

			if (lastCell == 0)
				lastCell = row.getLastCellNum();
			// System.out.println(sheet.getSheetName() + ",,,," + sheet.getWorkbook());
			// 不能用row.getPhysicalNumberOfCells()，可能会有空cell导致索引溢出
			// 使用row.getLastCellNum()至少可以保证索引不溢出，但会有很多Null值，如果使用集合的话，就不说了
			String[] cells = new String[lastCell -firstCell+1];
			
//			System.out.println("-----"+cells.length);
			// int last = 7;
			// int last = row.getLastCellNum();
			// System.out.println( row.getFirstCellNum()+"---"+row.getLastCellNum());
			if (firstCell == 0)
				firstCell = row.getFirstCellNum();
			if (firstCell < 0) {
				continue;
			}
//			 System.out.println(firstCell+"=="+row.getFirstCellNum()+",,,"+row.getRowNum()+",,,"+lastCell);
			boolean allempty = true;
			for (int c = firstCell; c <= lastCell; c++) {
				Cell cell = row.getCell(c);
				String value="";
			 //保证cells[]都有值,不会因为cell为null而出现非空值的情况
				if (cell == null) {
//					System.out.println("cell is null:" + r + "," + c);
					continue;
				}
				// System.out.println(cell+""+cell.getRowIndex()+",,,"+cell.getColumnIndex());
				boolean isMerge = isMergedRegion(sheet, cell.getRowIndex(), cell.getColumnIndex());
				if (isMerge) {
					value = getMergedRegionValue(sheet, cell.getRowIndex(), cell.getColumnIndex());

					// System.out.println(rs + "====" + row.getRowNum() + ",,," +
					// cell.getColumnIndex());
				} else
					value= getValueFromCell(cell); 
				if (!StringUtils.isEmpty(value))
					allempty = false;
//				System.out.println("---->>>"+cells[c]);
				cells[c-firstCell] =value;
			}
		
			list.add(cells);
			
			// 如果都为空，忽略掉这一行
			if (allempty) {

			}
			// 如果这一行的数据全为空的话，交给业务系统去处理
		}
		return list;
	}
	public static List<String[]> getOriginCells(Sheet sheet, boolean isShiftEmptyRow) {
		// 去掉空行。
		int lastrow = sheet.getLastRowNum();
		int first = sheet.getFirstRowNum();
		if (isShiftEmptyRow) {
			int i = lastrow;
			Row tempRow;
			while (i > 0) {
				i--;
				tempRow = sheet.getRow(i);
				if (tempRow == null) {
					sheet.shiftRows(i + 1, lastrow, -1);
				} else {
					boolean isEmpty = true;
					for (int c = tempRow.getFirstCellNum(); c <= tempRow.getLastCellNum(); c++) {
						Cell cell = tempRow.getCell(c);
						if (cell != null && cell.getCellType() != Cell.CELL_TYPE_BLANK) {
							isEmpty = false;
							continue;
						}
					}
					if (isEmpty) {
						sheet.shiftRows(i + 1, lastrow, -1);
					}
				}
			}
		}

		// 去掉空行。
		List<String[]> list = new ArrayList<String[]>();
		for (int r = first; r <= lastrow; r++) {
			Row row = sheet.getRow(r);
			if (row == null)
				continue;
			// 不能用row.getPhysicalNumberOfCells()，可能会有空cell导致索引溢出
			// 使用row.getLastCellNum()至少可以保证索引不溢出，但会有很多Null值，如果使用集合的话，就不说了
			int cellnum = row.getLastCellNum();
			int firstcell = row.getFirstCellNum();
			
//			 System.out.println(firstcell+"==------"+row.getFirstCellNum()+",,,"+row.getRowNum()+",,,"+cellnum+",,"+row.getLastCellNum());

			String[] cells = new String[cellnum];
			for (int c = firstcell; c <= cellnum; c++) {
				Cell cell = row.getCell(c);
				if (cell == null)
					continue;
				cells[c] = getValueFromCell(cell);
			}
			list.add(cells);
		}
		return list;
	}
	public static List<String[]> getCells(Sheet sheet, int firstRow, boolean isShiftEmptyRow) {

		Row row = sheet.getRow(firstRow);
		int lastrow = sheet.getLastRowNum();
		int columns = row.getLastCellNum();
		int firstCell = row.getFirstCellNum();
		return getCells(sheet, firstRow, lastrow, firstCell, columns, isShiftEmptyRow);
	}

	
	public static List<String[]> getCells(Sheet sheet, int firstRow, int firstCell, int lastCell,
			boolean isShiftEmptyRow) {
		
 		int lastrow = sheet.getLastRowNum();
		 
		return getCells(sheet, firstRow, lastrow, firstCell, lastCell, isShiftEmptyRow);
		
	}
	
	public static List<String[]> getCells(Sheet sheet, boolean isShiftEmptyRow) {
		int firstRow = sheet.getFirstRowNum();
		return getCells(sheet, firstRow, isShiftEmptyRow);
	}

	/**
	 * 获取合并单元格的值
	 * 
	 * @param sheet
	 * @param row
	 * @param column
	 * @return
	 */
	public static String getMergedRegionValue(Sheet sheet, int row, int column) {
		int sheetMergeCount = sheet.getNumMergedRegions();

		for (int i = 0; i < sheetMergeCount; i++) {
			CellRangeAddress ca = sheet.getMergedRegion(i);
			int firstColumn = ca.getFirstColumn();
			int lastColumn = ca.getLastColumn();
			int firstRow = ca.getFirstRow();
			int lastRow = ca.getLastRow();

			if (row >= firstRow && row <= lastRow) {

				if (column >= firstColumn && column <= lastColumn) {
					Row fRow = sheet.getRow(firstRow);
					Cell fCell = fRow.getCell(firstColumn);
					return getValueFromCell(fCell);
				}
			}
		}

		return null;
	}

	/**
	 * 获取单元格内文本信息
	 * 
	 * @param cell
	 * @return
	 * @date 2013-5-8
	 */
	public static String getValueFromCell(Cell cell) {
	 
//		 System.out.println(cell.getColumnIndex()+",,,"+cell.getCellType()+",,,"+cell.getCellTypeEnum());
	 
		if (cell == null) {
			return null;
		}
 
		String value = null;
		switch (cell.getCellType()) {

		case Cell.CELL_TYPE_NUMERIC: // 数字
			if (HSSFDateUtil.isCellDateFormatted(cell)) {// 如果是日期类型
				value = dateToString(cell.getDateCellValue(), "yyyy-MM-dd");
			} else {
				value = String.valueOf(cell.getNumericCellValue());
			}
			break;
		case Cell.CELL_TYPE_STRING: // 字符串
			value = cell.getStringCellValue();
			break;

		case Cell.CELL_TYPE_FORMULA: // 公式
			// 用数字方式获取公式结果，根据值判断是否为日期类型
			try {
				double numericValue = cell.getNumericCellValue();
				if (HSSFDateUtil.isCellDateFormatted(cell)) {
					value = dateToString(cell.getDateCellValue(), "yyyy-MM-dd");
				}
				// if (HSSFDateUtil.isValidExcelDate(numericValue)) { // 如果是日期类型
				// value = PrettyDateFormat.dateToString(cell.getDateCellValue(),
				// PrettyDateFormat.sdf);
				// }
				else {
					value = String.valueOf(numericValue);
				}
			} catch (Exception e) {
				value = cell.getStringCellValue();
			}
			break;
		case Cell.CELL_TYPE_BLANK:// 空白
			value = "";
			break;
		case Cell.CELL_TYPE_BOOLEAN:// Boolean
			value = String.valueOf(cell.getBooleanCellValue());
			break;
		case Cell.CELL_TYPE_ERROR:// Error，返回错误码
			value = String.valueOf(cell.getErrorCellValue());
			break;
		default:
			value = "";
			break;
		}
		return value;
	}

	public static String dateToString(Date date, String format) {
		DateFormat df = getDateFormat(format);
		synchronized (df) {
			return df.format(date);
		}
	}

	private static DateFormat getDateFormat(String format) {
		DateFormat fm = formatMap.get(format);
		if (fm == null) {
			fm = new SimpleDateFormat(format);
			formatMap.put(format, fm);
		}
		return fm;
	}

	private static Map<String, DateFormat> formatMap = new HashMap<String, DateFormat>();

}